# $Id: db.py ddcad87fab9c 2013/08/17 19:20:30 pgurumur $
# Copyright (c) 2009 Prabhu Gurumurthy  <pgurumur@gmail.com>
# 
# Permission to use, copy, modify, and distribute this software for any
# purpose with or without fee is hereby granted, provided that the above
# copyright notice and this permission notice appear in all copies.
#
# THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
# WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
# MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
# ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
# WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
# ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
# OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
#

__version__  = "$Revision: ddcad87fab9c $"
__author__   = "$Author: pgurumur $"
__modified__ = "$Date: 2013-08-17 19:20:30Z $"

# third party imports
import sqlite3
import MySQLdb

from exception import SQLError
import stdlib

# Database type supported by this class
MYSQL = 1
SQLITE = 2

# Main SQL class, takes in Database and Type as parameters
# In case of MySQL, database is the name, in case of SQLite, it is the
# filename, inheriting class, is responsible for setting the type
class sql(object):
   def __init__(self, Database, Type):
      # Local defines all are private
      self._database = None
      self._conn = None
      self._cursor = None
      self._type = None

      if Database:
         self._database = Database
      else:
         self.error("no database defined!")

      if Type:
         # Check to see whether the type is actually integer value, otherwise
         # we error out
         try:
            self._type = int(Type)
         except ValuError as err:
            self.error("integer expected")

   def __enter__(self):
      return self

   def __exit__(self, errtype, value, traceback):
      if hasattr(self, 'close'):
         self.close()

      if errtype and value:
         self.error("%s", str(value))

      return True

   def __repr__(self):
      return str(self)

   def __unicode__(self):
      return str(self).encode('utf-8')

   # Error handling function, closes the fd's before raising Error
   def error(self, fmt, *args):
      if hasattr(self, 'close'):
         self.close()

      raise SQLError(stdlib.sprintf(fmt, *args))

   def close(self):
      if self._conn:
         if self._cursor:
            self._cursor.close()
            self._cursor = None

         self._conn.close()
         self._conn = None

   # Unified execute function for MySQL and SQLite, raises error, when
   # there is a problem with the query, returns list
   def execute(self, querystring):
      retval = None
      if querystring:
         try:
            self._cursor.execute(querystring)
         except sqlite3.OperationalError as err:
            self.error("%s", err.message)
         except MySQLdb.Error as err:
            self._error("%s %d", err.args[1], err.args[0])
         else:
            if self._type == SQLITE:
               self._conn.commit()

            retval = self._cursor.fetchall()

      return retval

# MySQL child class
class mysql(sql):
   def __init__(self, Database, Host, User, Passwd = None, Port = 3306,
         Type = MYSQL):
      super(mysql, self).__init__(Database, Type)

      # Local private defines
      self._user = None
      self._host = None
      self._passwd = None
      self._conn = None
      self._cursor = None
      self._port = 0

      if Host:
         self._host = Host.strip()
      else:
         self.error("no host defined!")

      if User:
         self._user = User.strip()
      else:
         self.error("no user defined")
         raise SQLError, "no user defined"

      if Passwd:
         self._passwd = Passwd.strip()

      if Port:
         try:
            self._port = int(Port)
         except ValueError as err:
            self.error(err)

      # Try connecting to MySQL database, using the above mentioned parameters
      # If there are error, raise them otherwise, get the cursor to do
      # db/sql operations
      try:
         if self._passwd:
            self._conn = MySQLdb.connect(host = self._host,
                  port = self._port, user = self._user, passwd = self._passwd,
                  db = self._database)
         else:
            self._conn = MySQLdb.connect(host = self._host,
                  port = self._port, user = self._user, db = self._database)
      except MySQLdb.Error as err:
         self.error("%s %d", err.args[1], err.args[0])
      else:
         self._cursor = self._conn.cursor()

# SQLite child class
class sqlite(sql):
   def __init__(self, Database, Type = SQLITE):
      super(sqlite, self).__init__(Database, Type)

      try:
         self._conn = sqlite3.connect(self._database)
      except sqlite3.DatabaseError as err:
         self.error(err)
      else:
         self._cursor = self._conn.cursor()

   def __str__(self):
      return "sqlite connection to %s" %str(self._database)
